package com.sanalkitaplik.hibernateutil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Dictionary;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import net.sf.hibernate.Transaction;

import org.hibernate.Query;
import org.hibernate.Session;

import com.sanalkitaplik.packageclassdto.BookInfoDTO;
import com.sanalkitaplik.packageclassdto.BookResourcesDTO;
import com.sanalkitaplik.packageclassdto.ResourcesDTO;
import com.sanalkitaplik.packageclassdto.UsersResourcesDTO;
import com.sanalkitaplik.resources.Book;

public class ResourcesUtil {
	
	private static Session session = null;
	private static Transaction trans = null;
	private static Book bookInfoObject = null;
	private static ArrayList<String> bookTitleList = new ArrayList<String>();
	private static ArrayList<String> bookAuthorList = new ArrayList<String>();
	private static ArrayList<String> bookGenreList = new ArrayList<String>();
	private static ArrayList<String> bookDescriptionList = new ArrayList<String>();
	private static Connection connection = null;
	private static Statement stmt = null;
	private static ResultSet rs = null;
	
	@SuppressWarnings("rawtypes")
	public static ArrayList<Book> getResourceInfo(String username){
		try{
			session = SessionFactoryBuilder.getSessionFactory().openSession();
			
			Query titleQuery = session.createSQLQuery(
				"SELECT BI.TITLE AS T, SA.AUTHOR_NAME AS A FROM SNLKTPLK_USERS_LOGIN_INFO AS US " +
				"JOIN SNLKTPLK_USERS_RESOURCES AS UR ON US.USER_ID = UR.USER_ID JOIN " +
				"SNLKTPLK_RESOURCES AS RE ON UR.RESOURCE_ID = RE.RESOURCE_ID JOIN " +
				"SNLKTPLK_RESOURCE_TYPES AS RT ON RE.TYPE_ID = RT.TYPE_ID JOIN " +
				"SNLKTPLK_BOOK_RESOURCES AS BR ON RE.RESOURCE_ID = BR.RESOURCE_ID JOIN " +
				"SNLKTPLK_BOOK_EDITIONS AS BE ON BR.BOOK_EDITION_ID = BE.BOOK_EDITION_ID JOIN " +
				"SNLKTPLK_BOOK_INFO AS BI ON BE.BOOK_ID = BI.BOOK_ID JOIN " +
				"SNLKTPLK_GENRES AS GE ON BI.GENRE_ID = GE.GENRE_ID JOIN SNLKTPLK_BOOK_AUTHOR AS BA " +
				"ON BI.BOOK_ID = BA.BOOK_ID JOIN SNLKTPLK_AUTHORS AS SA ON BA.AUTHOR_ID = SA.AUTHOR_ID " +
				"WHERE US.USERNAME = '"+username+"' and RT.TYPE_NAME = 'Book'"
				);
			
			List titleList = titleQuery.list();
			
			for (Iterator iterator = titleList.iterator(); iterator.hasNext();) {
				bookTitleList.add((String) iterator.next());
			}
			
			ResultSet rs = stmt.executeQuery(
					"select a.AUTHOR_NAME, ba.BOOK_ID from SNLKTPLK_AUTHORS as a join SNLKTPLK_BOOK_AUTHOR as ba " +
					"on a.AUTHOR_ID=ba.AUTHOR_ID where a.BOOK_ID in " +
					"(select r.RESOURCE_ID from SNLKTPLK_RESOURCES as r join SNLKTPLK_USER_RESOURCES as ur " +
					"on r.RESOURCE_ID=ur.RESOURCE_ID where ur.USER_ID=" +
					"(select USER_ID from SNLKTPLK_USERS_LOGIN_INFO where USERNAME='"+username+"') " +
					"and r.TYPE_ID=(select TYPE_ID from SNLKTPLK_RESOURCE_TYPES where TYPE_NAME='Book')) " +
					"order by ba.BOOK_ID"
					);
			
			Query genreQuery = session.createSQLQuery(
					"select g.GENRE_NAME from SNLKTPLK_GENRES as g join " +
					"SNLKTPLK_BOOK_INFO as bi on g.GENRE_ID=bi.GENRE_ID where bi.BOOK_ID in " +
					"(select r.RESOURCE_ID from SNLKTPLK_RESOURCES as r " +
					"join SNLKTPLK_USER_RESOURCES as ur on r.RESOURCE_ID=ur.RESOURCE_ID " +
					"where ur.USER_ID=(select USER_ID from SNLKTPLK_USERS_LOGIN_INFO where USERNAME='"+username+"') " +
					"and r.TYPE_ID=(select TYPE_ID from SNLKTPLK_RESOURCE_TYPES where TYPE_NAME='Book'))"
					);
			
			List genreList = genreQuery.list();
			
			for (Iterator iterator = genreList.iterator(); iterator.hasNext();) {
				bookGenreList.add((String) iterator.next());
			}
			
			Query desciptionQuery = session.createSQLQuery(
					"select r.PRIORITY, r.RATE, r.PRICE, r.DESCRIPTION, r.STATE from SNLKTPLK_RESOURCES as r " +
					"join SNLKTPLK_USER_RESOURCES as ur on r.RESOURCE_ID=ur.RESOURCE_ID " +
					"where ur.USER_ID=(select USER_ID from SNLKTPLK_USERS_LOGIN_INFO where USERNAME='"+username+"') " +
					"and r.TYPE_ID=(select TYPE_ID from SNLKTPLK_RESOURCE_TYPES where TYPE_NAME='Book')"
					);
			
			bookInfoObject = new Book();
			
			
		}
		catch(Exception ex){
			System.out.println("An exception occured while getting resource info : " +ex.getMessage());
		}
		finally{
			session.flush();
			session.close();
		}
	}

	public static boolean addBookToTheSystem(ResourcesDTO rdto,BookInfoDTO bidto,BookResourcesDTO brdto,UsersResourcesDTO urdto) throws SQLException{
		boolean flag = true;
		try{
			connection = Connect.getConnection();
			stmt = connection.createStatement();

			String sql = "insert into SNLKTPLK_ values (default,'')";
	    	stmt.executeUpdate(sql);
		}
		catch(Exception ex){
			System.out.println("An exception occured while adding a book to the system : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
		}
		return flag;
	}
	
	@SuppressWarnings("rawtypes")
	public static boolean deleteBookFromTheSystem(String username, String title) throws SQLException{
		boolean flag = true;
		long resource_id = 0;
		try{
			connection = Connect.getConnection();
			stmt = connection.createStatement();
			
			rs = stmt.executeQuery(
					"select ur.RESOURCE_ID from SNLKTPLK_USERS_RESOURCES as ur join SNLKTPLK_BOOK_INFO as bi " +
					"on ur.RESOURCE_ID=bi.BOOK_ID where " +
					"ur.USER_ID=(SELECT USER_ID FROM SNLKTPLK_USERS_LOGIN_INFO where USERNAME='"+username+"') " +
					"and bi.TITLE='"+title+"'"
					);
			if(rs.next())
				resource_id = rs.getLong("RESOURCE_ID");
			
			rs = stmt.executeQuery("delete from SNLKTPLK_BOOK_INFO where BOOK_ID="+resource_id+"");
			rs = stmt.executeQuery("delete from SNLKTPLK_BOOK_RESOURCES where RESOURCE_ID="+resource_id+"");
			rs = stmt.executeQuery("delete from SNLKTPLK_RESOURCES where RESOURCE_ID="+resource_id+"");
			rs = stmt.executeQuery("delete from SNLKTPLK_USERS_RESOURCES where RESOURCE_ID="+resource_id+"");
		}
		catch(Exception ex){
			flag = false;
			System.out.println("An exception occured while removing a book to the system : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
			rs.close();
		}
		return flag;
	}
}
